package tech.yunland.shop.domain.service;

import com.moyuer.bsf.domain.model.HqlArgs;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import tech.yunland.shop.domain.dao.GoodsStockDao;
import tech.yunland.shop.domain.model.GoodsStock;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;

/**
 * 商品仓库服务类
 *
 * @author 文卡<wkwenka@gmail.com>  on 17-3-24.
 */
@Service
public class GoodsStockService {

    @Autowired
    private GoodsStockDao goodsStockDao;

    /**
     * 自动生成 hql
     *
     * @param param
     * @param goodsIds
     * @param organId
     * @param states
     * @return
     */
    private HqlArgs getHqlArgs(String param, List<String> goodsIds, String organId, List<Integer> states) {
        param = StringUtils.trimToEmpty(param);

        Map<String, Object> args = new HashMap<>();

        String hql = "FROM GoodsStock WHERE 1=1";

        if (goodsIds != null && goodsIds.size() > 0) {
            if (goodsIds.size() == 1) {
                hql += " AND goods.id = :goodsId";
                args.put("goodsId", goodsIds.get(0));
            } else {
                hql += " AND goods.id IN :goodsId";
                args.put("goodsId", goodsIds);
            }
        }

        hql += " AND organ.id = :organIds";
        args.put("organIds", organId);

        if (states != null && states.size() > 0) {
            if (states.size() == 1) {
                hql += " AND state = :states";
                args.put("states", states.get(0));
            } else {
                hql += " AND state IN :states";
                args.put("states", states);
            }
        }

        if (StringUtils.isNotBlank(param)) {
            hql += " AND (goods.name LIKE :param OR goods.spell LIKE :param)";
            args.put("param", "%" + param + "%");
        }

        hql += " ORDER BY goods.name ASC, storeNum DESC";

        return new

                HqlArgs(hql, args);

    }

    /**
     * 保存/更新倉庫
     *
     * @param goodsStock
     */
    public void saveOrUpdateStock(GoodsStock goodsStock) {

        List<String> goodsIds = new LinkedList<>();
        goodsIds.add(goodsStock.getGoodsId());

        List<Integer> states = new LinkedList<>();
        states.add(goodsStock.getState());

        //查看该商品是否在仓库种存在
        HqlArgs hqlArgs = getHqlArgs(null, goodsIds, goodsStock.getOrganId(), states);
        List<GoodsStock> goodsStockList = goodsStockDao.findByNamedParam(hqlArgs.getHql(), hqlArgs.getArgs());

        if (goodsStockList != null && goodsStockList.size() > 0) {
            if (goodsStockList.size() == 1) {
                GoodsStock stock = goodsStockList.get(0);
                double v = stock.getStoreNum() + goodsStock.getStoreNum();
                if (v < 0) {
//                    throw new RuntimeException(stock.getGoodsName() + "库存不足");
                }
                stock.setStoreNum(v);
                goodsStockDao.update(stock);
            } else {
                throw new RuntimeException("仓库数据异常");
            }
        } else {
            if (goodsStock != null && goodsStock.getState() != null && goodsStock.getStoreNum() != null) {
                if (goodsStock.getState().intValue() == 1 && goodsStock.getStoreNum().intValue() < 0) {
                //    throw new RuntimeException(goodsStock.getGoodsName() + "库存不足");
                }
            }

            GoodsStock stock = new GoodsStock();
            stock.setGoods(goodsStock.getGoods());
            stock.setOrgan(goodsStock.getOrgan());
            stock.setStoreNum(goodsStock.getStoreNum());
            stock.setState(goodsStock.getState());
            goodsStockDao.save(stock);
        }
    }

    /**
     * 根据条件获取库存信息
     *
     * @param goodsIds
     * @param organId
     * @param state
     * @return
     */
    public List<GoodsStock> getStoreList(String param, List<String> goodsIds, String organId, List<Integer> state, Integer startIndex, Integer rows) {

        HqlArgs hqlArgs = getHqlArgs(param, goodsIds, organId, state);
        List<GoodsStock> byNamedParam = goodsStockDao.findByNamedParam(hqlArgs.getHql(), startIndex, rows, hqlArgs.getArgs());
        return byNamedParam;
    }

    /**
     * 獲取列表數量
     *
     * @param param
     * @param goodsIds
     * @param organId
     * @param state
     * @return
     */
    public Integer getStoreListSize(String param, List<String> goodsIds, String organId, List<Integer> state) {

        HqlArgs hqlArgs = getHqlArgs(param, goodsIds, organId, state);
        List<GoodsStock> byNamedParam = goodsStockDao.findByNamedParam(hqlArgs.getHql(), hqlArgs.getArgs());
        return byNamedParam != null ? byNamedParam.size() : 0;
    }

    /**
     * 获取可用库存
     *
     * @param goodsIds
     * @param organId
     * @param param
     * @param startIndex
     * @param rows
     * @return
     */
    public List<Map<String, Object>> getUseStock(List<String> goodsIds, String organId, String param, Integer startIndex, Integer rows) {
        return goodsStockDao.getUseStock(goodsIds, organId, param, startIndex, rows);
    }

    public int getUseStockSize(List<String> goodsIds, String organId, String param) {
        return goodsStockDao.getUseStock(goodsIds, organId, param, null, null).size();
    }

    /**
     * 判断是否有可用库存
     *
     * @param quantity
     * @param goods_id
     * @param organId
     */
    public boolean isHasUsed(BigDecimal quantity, String goods_id,String organId) {
        List<String> goodids = new LinkedList<>();
        goodids.add(goods_id);
        List<Map<String, Object>> useStock = this.getUseStock(goodids, organId, "", null, null);
        if (useStock != null && useStock.size() > 0) {
            Map<String, Object> stringObjectMap = useStock.get(0);
            Object sum = stringObjectMap.get("sum");
            if (ObjectUtils.notEqual(sum, null)) {
                if (quantity.intValue() > ((BigDecimal) sum).intValue()) {
                    return false;
                }

            } else {
                return false;
            }
        }else {
            return false;
        }

        return true;
    }

    /**
     * 获取日报表的总条数
     *
     * @param param
     * @param date
     * @param organIds
     * @return
     */
    public Long getDayReportsGoodStoreCount(String param, String date, List<String> organIds){
        String oids = StringUtils.join(organIds,"','");
        String sql = "select count(1) as counts\n" +
                "FROM (\n" +
                "  SELECT\n" +
                "    organ.name         oname \n" +
                "    ,\n" +
                "    good.name          gname \n" +
                "    ,\n" +
                "    sum(gs.store_num)  snm \n" +
                "    ,\n" +
                "    sum(gs.in_transit) itt \n" +
                "    ,\n" +
                "    sum(gs.freeze_up)  fup \n" +
                "  FROM goods_store gs\n" +
                "    , goods good\n" +
                "    , bsf_organ organ\n" +
                "  WHERE\n" +
                "    to_char(gs.create_time,'yyyy-MM-dd') = ?\n" +
                "    and gs.organ_id in('"+oids+"')\n" +
                "    and gs.goods_id = good.id\n" +
                "    AND gs.organ_id = organ.id\n";
        if (StringUtils.isNotBlank(param)){
            sql += " AND (organ.name like '%"+param+"%' OR good.name like '%"+param+"%')";
        }

        sql +=  "           GROUP BY organ.name,good.name,organ.id,good.id\n" ;
        sql +=  "      ) c";
        BigInteger counts = (BigInteger) goodsStockDao.findUniqueBySQL(sql,date).get("counts");
        return counts.longValue();

    }

    /**
     *
     * 获取日报表
     *
     * @param param
     * @param date
     * @param organIds
     * @param startIndex
     * @param rows
     * @return
     */
    public List<Map<String,Object>> getDayReportGoodStore(String param, String date, List<String> organIds, Integer startIndex, Integer rows){
            String oids = StringUtils.join(organIds,"','");
            param = StringUtils.trimToEmpty(param);
            String sql = "       select organ.name oname \n" +
                    "             ,good.name  gname \n" +
                    "             ,sum(gs.store_num) snm \n" +
                    "             ,sum(gs.in_transit) itt \n" +
                    "             ,sum(gs.freeze_up) fup  \n" +
                    "         from goods_store gs\n" +
                    "             ,goods good\n" +
                    "             ,bsf_organ organ\n" +
                    "         where to_char(gs.create_time,'yyyy-MM-dd') = ?\n" +
                    "           and gs.organ_id in('"+oids+"')\n" +
                    "           and gs.goods_id = good.id\n" +
                    "           and gs.organ_id = organ.id\n";

            if (StringUtils.isNotBlank(param)){
                sql += " AND (organ.name like '%"+param+"%' OR good.name like '%"+param+"%')";
            }

            sql +=  "           GROUP BY organ.name,good.name,organ.id,good.id\n" ;
            sql +=  "           order by organ.id";
        List<Map<String,Object>> dayReports =   goodsStockDao.findBySQL(sql,startIndex,rows,date);
        return dayReports;
    }

    /**
     * 获取月报表的条数
     *
     * @param param
     * @param date
     * @param organIds
     * @return
     */
    public Long getMonthReportsGoodStoreCount(String param, String date, List<String> organIds){
        String oids = StringUtils.join(organIds,"','");
        String sql = "\n" +
                "select count(1) counts from (\n" +
                "  SELECT\n" +
                "    stores.oname,\n" +
                "    stores.gname,\n" +
                "    sum(stores.snm),\n" +
                "    sum(stores.itt),\n" +
                "    sum(stores.fup)\n" +
                "\n" +
                "  FROM (\n" +
                "         SELECT\n" +
                "           organ.name                  oname \n" +
                "           ,\n" +
                "           CASE WHEN good.name IS NULL\n" +
                "             THEN '无商品'\n" +
                "           ELSE good.name END          gname \n" +
                "           ,\n" +
                "           CASE WHEN sum(gs.store_num) IS NULL\n" +
                "             THEN 0\n" +
                "           ELSE sum(gs.store_num) END  snm \n" +
                "           ,\n" +
                "           CASE WHEN sum(gs.in_transit) IS NULL\n" +
                "             THEN 0\n" +
                "           ELSE sum(gs.in_transit) END itt \n" +
                "           ,\n" +
                "           CASE WHEN sum(gs.freeze_up) IS NULL\n" +
                "             THEN 0\n" +
                "           ELSE sum(gs.freeze_up) END  fup \n" +
                "\n" +
                "      FROM (SELECT *\n" +
                "            FROM bsf_organ\n" +
                "            WHERE\n" +
//                "              to_char(create_time, 'yyyy-MM') = ? and\n" +
                "                id in('"+oids+"')\n" +
                "           ) organ\n" +
                "        LEFT JOIN goods_store gs\n" +
                "          ON gs.organ_id = organ.id\n" +
                "        LEFT JOIN goods good\n" +
                "          ON gs.goods_id = good.id\n" +
                "       WHERE to_char(gs.create_time, 'yyyy-MM') = ?\n"+
                "\n" +
                "         GROUP BY organ.name\n" +
                "           , CASE WHEN good.name IS NULL\n" +
                "           THEN '无商品'\n" +
                "             ELSE good.name END\n" +
                "           , organ.id\n" +
                "           , good.id\n" +
                "         ORDER BY organ.name\n" +
                "       ) stores\n" +
                "    WHERE 1=1\n";
        if (StringUtils.isNotBlank(param)){
            sql += " AND stores.gname like '%" + param + "%' OR stores.oname like '%" + param + "%'";
        }
        sql += "    group by stores.oname,stores.gname";
        sql += "    ) out";
        BigInteger counts = (BigInteger) goodsStockDao.findUniqueBySQL(sql,date).get("counts");
        return counts.longValue();

    }

    /**
     *
     * 获取月报表
     *
     * @param param
     * @param date
     * @param organIds
     * @param startIndex
     * @param rows
     * @return
     */
    public List<Map<String,Object>> getMonthReportGoodStore(String param, String date, List<String> organIds, Integer startIndex, Integer rows){
        String oids = StringUtils.join(organIds,"','");
        param = StringUtils.trimToEmpty(param);
        String sql = "select\n" +
                "      stores.oname,\n" +
                "      stores.gname,\n" +
                "      sum(stores.snm) snm,\n" +
                "      sum(stores.itt) itt,\n" +
                "      sum(stores.fup) fup\n" +
                "\n" +
                "    from (\n" +
                "      SELECT\n" +
                "        organ.name         oname \n" +
                "        ,\n" +
                "        case when good.name is null then '无商品' else good.name end          gname \n" +
                "        ,\n" +
                "        case when sum(gs.store_num) is null then 0 else sum(gs.store_num) end  snm \n" +
                "        ,\n" +
                "        case when sum(gs.in_transit) is null then 0 else sum(gs.in_transit) end itt \n" +
                "        ,\n" +
                "        case when sum(gs.freeze_up) is null then 0 else sum(gs.freeze_up) end  fup \n" +
                "\n" +
                "      FROM (SELECT *\n" +
                "            FROM bsf_organ\n" +
                "            WHERE\n" +
//                "              to_char(create_time, 'yyyy-MM') = ? and\n" +
                "                id in('"+oids+"')\n" +
                "           ) organ\n" +
                "        LEFT JOIN goods_store gs\n" +
                "          ON gs.organ_id = organ.id\n" +
                "        LEFT JOIN goods good\n" +
                "          ON gs.goods_id = good.id\n" +
                "       WHERE to_char(gs.create_time, 'yyyy-MM') = ?\n"+
                "\n" +
                "      GROUP BY organ.name\n" +
                "        , case when good.name is null then '无商品' else good.name end\n" +
                "        , organ.id\n" +
                "        , good.id\n" +
                "      order by organ.name\n" +
                "    ) stores\n" +
                "    WHERE 1=1\n";
        if (StringUtils.isNotBlank(param)){
            sql += " AND stores.gname like '%" + param + "%' OR stores.oname like '%" + param + "%'";
        }
        sql += "    group by stores.oname,stores.gname";
        List<Map<String,Object>> dayReports =   goodsStockDao.findBySQL(sql,startIndex,rows,date);
        return dayReports;
    }
    /**
     * 获取总报表的条数
     *
     * @param param
     * @param organIds
     * @return
     */
    public BigInteger getAllReportsGoodStoreCount(String param, List<String> organIds){
        String oids = StringUtils.join(organIds,"','");
        param = StringUtils.trimToEmpty(param);
        String sql = "\n" +
                "select count(1) counts from (\n" +
                "  SELECT\n" +
                "    stores.oname,\n" +
                "    stores.gname,\n" +
                "    sum(stores.snm),\n" +
                "    sum(stores.itt),\n" +
                "    sum(stores.fup)\n" +
                "\n" +
                "  FROM (\n" +
                "         SELECT\n" +
                "           organ.name                  oname \n" +
                "           ,\n" +
                "           CASE WHEN good.name IS NULL\n" +
                "             THEN '无商品'\n" +
                "           ELSE good.name END          gname \n" +
                "           ,\n" +
                "           CASE WHEN sum(gs.store_num) IS NULL\n" +
                "             THEN 0\n" +
                "           ELSE sum(gs.store_num) END  snm \n" +
                "           ,\n" +
                "           CASE WHEN sum(gs.in_transit) IS NULL\n" +
                "             THEN 0\n" +
                "           ELSE sum(gs.in_transit) END itt \n" +
                "           ,\n" +
                "           CASE WHEN sum(gs.freeze_up) IS NULL\n" +
                "             THEN 0\n" +
                "           ELSE sum(gs.freeze_up) END  fup \n" +
                "\n" +
                "      FROM (SELECT *\n" +
                "            FROM bsf_organ\n" +
                "            WHERE\n" +
                "              id in('"+oids+"')\n" +
                "           ) organ\n" +
                "        LEFT JOIN goods_store gs \n" +
                "          ON gs.organ_id = organ.id\n" +
                "        LEFT JOIN goods good\n" +
                "          ON gs.goods_id = good.id\n" +
                "\n" +
                "         GROUP BY organ.name\n" +
                "           , CASE WHEN good.name IS NULL\n" +
                "           THEN '无商品'\n" +
                "             ELSE good.name END\n" +
                "           , organ.id\n" +
                "           , good.id\n" +
                "         ORDER BY organ.name\n" +
                "       ) stores\n" +
                "    WHERE 1=1\n";
        if (StringUtils.isNotBlank(param)){
            sql += " AND stores.gname like '%" + param + "%' OR stores.oname like '%" + param + "%'";
        }
        sql += "    group by stores.oname,stores.gname";
        sql += "    ) out";
        BigInteger counts = (BigInteger) goodsStockDao.findUniqueBySQL(sql).get("counts");
        return counts;

    }

    /**
     *
     * 获取总报表
     *
     * @param param
     * @param organIds
     * @param startIndex
     * @param rows
     * @return
     */
    public List<Map<String,Object>> getAllReportGoodStore(String param, List<String> organIds, Integer startIndex, Integer rows){
        String oids = StringUtils.join(organIds,"','");
        param = StringUtils.trimToEmpty(param);
        String sql = "select\n" +
                "      stores.oname,\n" +
                "      stores.gname,\n" +
                "      sum(stores.snm) snm,\n" +
                "      sum(stores.itt) itt,\n" +
                "      sum(stores.fup) fup\n" +
                "\n" +
                "    from (\n" +
                "      SELECT\n" +
                "        organ.name         oname --所属仓库\n" +
                "        ,\n" +
                "        case when good.name is null then '无商品' else good.name end          gname --商品\n" +
                "        ,\n" +
                "        case when sum(gs.store_num) is null then 0 else sum(gs.store_num) end  snm --库存和\n" +
                "        ,\n" +
                "        case when sum(gs.in_transit) is null then 0 else sum(gs.in_transit) end itt --在途和\n" +
                "        ,\n" +
                "        case when sum(gs.freeze_up) is null then 0 else sum(gs.freeze_up) end  fup --冻结和\n" +
                "\n" +
                "      FROM (SELECT *\n" +
                "            FROM bsf_organ\n" +
                "            WHERE\n" +

                "              id in('"+oids+"')\n" +
                "           ) organ\n" +
                "        LEFT JOIN goods_store gs \n" +
                "          ON gs.organ_id = organ.id\n" +
                "        LEFT JOIN goods good\n" +
                "          ON gs.goods_id = good.id\n" +
                "\n" +
                "      GROUP BY organ.name\n" +
                "        , case when good.name is null then '无商品' else good.name end\n" +
                "        , organ.id\n" +
                "        , good.id\n" +
                "      order by organ.name\n" +
                "    ) stores\n" +
                "    WHERE 1=1\n";

        if (StringUtils.isNotBlank(param)){
            sql += " AND stores.gname like '%" + param + "%' OR stores.oname like '%" + param + "%'";
        }

        sql += "    group by stores.oname,stores.gname";
        List<Map<String,Object>> dayReports =   goodsStockDao.findBySQL(sql,startIndex,rows);
        return dayReports;
    }
}
